set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode = 200;
set hive.exec.max.dynamic.partitions = 200;
with detail_tmp as ( --以网点发件时间统计的明细
    select
         waybill_no
        ,send_site_code
        ,send_site
        ,is_small
        ,is_slender
        ,is_own_io
        ,same_bill
        ,is_same_city
        ,bagging_scan_time
        --,package_code
        ,send_scan_time3
        ,bagging_user_code
        ,bagging_user
        ,bag_package_code
    from jms_dm.dm_network_bagging_detail_send_dt  --发件明细
    where  (dt = date_add('{{ execution_date | cst_ds }}',-2) or dt = '{{ execution_date | cst_ds }}')
    --and (is_small=1 or is_slender=1 ) and is_own_io!=1 and is_same_city!=1 and same_bill >=10 and bagging_scan_time is not null
    --and is_need_bagging_no =1
    and (case when (is_small=1 or is_slender=1 or is_special=1 or is_lightweight=1) and is_own_io!=1 and is_same_city!=1 and same_bill >=10
              --集散a到集散b到中心 a建包b没建包 不计入需建包
              and  send_site_type=3 and next_network_type=2 and is_up_site_baged =1 and  bagging_scan_time is  null
              then 0
              when (is_small=1 or is_slender=1 or is_special=1 or is_lightweight=1) and is_own_io!=1 and is_same_city!=1 and same_bill >=10
              then  1
              else 0
              end)=1
    and bagging_scan_time is not null
)

insert overwrite table jms_dm.dm_network_bagging_user_cnt_dt partition(dt)
select
t.date_time  
,net.virt_code     ---虚拟代理区code
,net.virt_name     ---虚拟代理区name
,net.agent_code    ---代理区编码
,net.agent_name    ---代理区名称
,net.fran_code     ---加盟商编码
,net.fran_name     ---加盟商名称
,net.provider_id   ---省份ID
,net.provider_desc ---省份
,net.city_id       ---城市ID
,net.city_desc     ---城市
,net.area_id       ---区/县id
,net.area_desc     ---区/县
,t.send_site_code as network_code
,net.name as network_name
,t.bagging_user_code
,t.bagging_user_name
,t.bagging_qty   --已建包票数
,t.package_qty   --建包数(包号去重)
,sum(bagging_qty) over(partition by date_time,send_site_code) as net_bagging_qty --网点总票数
,t.date_time as dt
from 
(
   select 
    date(send_scan_time3) as date_time
   ,send_site_code
   ,bagging_user_code
   ,max(bagging_user) as bagging_user_name
   ,count(1) as bagging_qty 
   ,count(distinct bag_package_code) as package_qty
   from detail_tmp
   group by date(send_scan_time3),send_site_code,bagging_user_code
)t 
left join jms_dim.dim_network_whole_massage net on net.code=t.send_site_code
distribute by pmod(hash(rand()), 3)
;
